//
//  LibSqlite.m
//  ios_news
//
//  Created by Han on 14-6-25.
//  Copyright (c) 2014年 ucoder. All rights reserved.
//

#import "LibSqlite.h"


@implementation LibSqlite


+ (LibSqlite *) shareInstance {
    
    LibSqlite *instance;

    instance = [[self alloc] init];
    
    /*
    static LibSqlite *instance;
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        instance = [[self alloc] init];
    });
    */
    
    return instance;
}

- (id)init
{
    // 初始化数据库
    [self initDB];
	return self;
}


- (BOOL) initDB
{
    // 获取document目录并返回数据库目录
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
	NSString *documentsDirectory = [paths objectAtIndex:0];
	NSString *path = [documentsDirectory stringByAppendingPathComponent:@"ucoder.sql"];
    //NSLog(@"%@", path);
    
    NSFileManager *fileManager = [NSFileManager defaultManager];
    //判断数据库是否存在
    BOOL dbExist = [fileManager fileExistsAtPath:path];
    
    //如果数据库存在，则用sqlite3_open直接打开
    if (dbExist) {
        
        //NSLog(@"Database file have already existed.");
        if(sqlite3_open([path UTF8String], &mDatabase) != SQLITE_OK) {
            
            //如果打开数据库失败则关闭数据库
            sqlite3_close(mDatabase);
            NSLog(@"Error: open database file.");
            return NO;
        }
        
        return YES;
    }
    
    
    
    // 打开数据库，这里的[path UTF8String]是将NSString转换为C字符串，因为SQLite3是采用可移植的C
	if(sqlite3_open([path UTF8String], &mDatabase) == SQLITE_OK) {
		// 初始化表
        
        // 收藏表
        char *sql = "CREATE TABLE IF NOT EXISTS `favorite_news` (`fav_id` INTEGER PRIMARY KEY AUTOINCREMENT, `type` VARCHAR, `news_id` INTEGER, `news_title` VARCHAR, `news_image_url` VARCHAR, `news_content` TEXT, `news_created` VARCHAR)";
        [self createTable:sql];
        return YES;
        
    } else {
		//如果创建并打开数据库失败则关闭数据库
		sqlite3_close(mDatabase);
		NSLog(@"Error: open database failure.");
        return NO;
    }
}

- (void) createTable:(char *)sql {
	
	sqlite3_stmt *statement;
	NSInteger sqlReturn = sqlite3_prepare_v2(mDatabase, sql, -1, &statement, nil);
	
	//如果SQL语句解析出错的话程序返回
	if(sqlReturn != SQLITE_OK) {
		NSLog(@"Error: failed to prepare statement:create table");
		return;
	}
	
	//执行SQL语句
	int success = sqlite3_step(statement);
	//释放sqlite3_stmt
	sqlite3_finalize(statement);
	
	//执行SQL语句失败
	if (success != SQLITE_DONE) {
		NSLog(@"Error: failed to dehydrate:create table");
		return;
	}
}

// insert delete update
- (BOOL) execute:(NSString *)sql
{
    char *sqlChar = (char *)[sql UTF8String];
    
    sqlite3_stmt *statement;
    
    //NSLog(@"%@", mDatabase);
    int success = sqlite3_prepare_v2(mDatabase, sqlChar, -1, &statement, NULL);

    if (success != SQLITE_OK) {
        NSLog(@"Error: execute - failed to prepare.");
        sqlite3_close(mDatabase);
        return NO;
    }
    
    //执行语句
    success = sqlite3_step(statement);
    //释放statement
    sqlite3_finalize(statement);
    
    //如果失败
    if (success == SQLITE_ERROR) {
        NSLog(@"Error: failed to execute.");
        //关闭数据库
        sqlite3_close(mDatabase);
        return NO;
    }
    //关闭数据库
    sqlite3_close(mDatabase);
    return YES;
}

- (int) getCount:(NSString *)sql {
    char *sqlChar = (char *)[sql UTF8String];
		
    sqlite3_stmt *statement = nil;
		
    int success = sqlite3_prepare_v2(mDatabase, sqlChar, -1, &statement, NULL);
    
    if (success != SQLITE_OK) {
        NSLog(@"Error: getCount - failed to prepare. - %d", success);
        sqlite3_close(mDatabase);
        return -1;
    }

    int count = 0;
    while (sqlite3_step(statement) == SQLITE_ROW) {
        count  = (int)sqlite3_column_int(statement, 0);
    }

    sqlite3_finalize(statement);
    sqlite3_close(mDatabase);
    
    return count;
}

- (NSMutableArray *) getAll:(NSString *)sql {
    
    char *sqlChar = (char *)[sql UTF8String];
	NSMutableArray *result = [NSMutableArray arrayWithCapacity:0];
		
    sqlite3_stmt *statement = nil;
		
    int success = sqlite3_prepare_v2(mDatabase, sqlChar, -1, &statement, NULL);
    
    if (success != SQLITE_OK) {
        NSLog(@"Error: getAll - failed to prepare.");
        sqlite3_close(mDatabase);
        return NO;
    }
    
    
    @try{
        int count = sqlite3_column_count(statement);
        //查询结果集中一条一条的遍历所有的记录，这里的数字对应的是列值。
        while (sqlite3_step(statement) == SQLITE_ROW) {
            NSMutableArray *arr = [NSMutableArray arrayWithCapacity:count];
            for (int i=0; i<count; i++) {

                const char *c = (const char *)sqlite3_column_text(statement, i);
                if (c) {
                    [arr addObject:[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, i)]];
                } else {
                    [arr addObject:@""];
                }
                
            }
            [result addObject:arr];
            //[arr release];
        }
    }
    @catch(NSException *exception) {
        NSLog(@"exception:%@", exception);
    }
    @finally {
        
    }

    

    
    sqlite3_finalize(statement);
    sqlite3_close(mDatabase);
	
	return result;
}


@end
